<?php

class GeoCode {
    var $api_key = "ABQIAAAAJZttMmgPcPiHwdL8qWnD2xRd3JUOXrtn_whYkiWYpdyHamab8RTTxN9W5FhdT0orQh4BeV7Ov0zLiA";
    var $map_url = "http://maps.google.com/maps/geo?q={ADDRESS}&output=csv&oe=utf8&sensor=false&key={KEY}";
    
    function GeoCode () {
        $this->map_url = str_replace("{KEY}", $this->api_key, $this->map_url);
    }
    
    function getGeoCode($address = '') {
        $content = "";
        if (trim($address)) {
            $url = str_replace("{ADDRESS}", urlencode($address), $this->map_url);
//            echo $url . "\n";
            if ($fp = fopen($url, "rb")) {
                while (!feof($fp)) {
                    $content .= fread($fp, 8192);
                }
                fclose($fp);
            }
        }
        return $content;
    }
    
    function parseGeoCode($coord = '') {
        if ($coord) {
            $t_coord = explode("\n", $coord);
            if ($t_coord[0]) {
                $t_row = explode(",", $t_coord[0]);
                return $t_row[2] . "," . $t_row[3];
            }
        }
        return false;
    }
}

class PlaceCoord {
    private $db = false;
    private $gc = false;
    
    var $repl_list = array(
        " pst." => " puiestee ",
        " pst " => " puiestee ",
        " mnt." => " maantee ",
        " mnt " => " maantee ",
        " tn." => " ",
        " tn " => " ",
        "  " => " "
    );
    
    function PlaceCoord($db) {
        $this->db = $db;
        $this->gc = new GeoCode();
    }
    
    function fixAddr($addr) {
        $t_addr = $addr;
        foreach ($this->repl_list as $r_src => $r_tar) {
            $t_addr = str_replace($r_src, $r_tar, $t_addr);
        }
        return $t_addr;
    }
    
    function assignCoord() {
        $cnt = 0;
        
        $suffix = ', Tallinn, Estonia';
        $sql = "SELECT city2.street, city2.housenum, count(*), IF(address.id, 1, 0) as address_exists FROM city2 LEFT JOIN address ON city2.street = address.street AND city2.housenum = address.housenum WHERE 1 GROUP BY city2.street, city2.housenum";
        foreach ($this->db->query($sql) as $data) {
            if ($data['address_exists']) {
                continue;
            }
            $t_addr = trim($data["street"]) . ' ' . trim($data['housenum']);
            
            if (!$t_addr) {
                continue;
            }
            $cnt++;
            $t_addr = $this->fixAddr($t_addr);
            echo $cnt . ": " . $t_addr;
            $t_addr = $t_addr . $suffix;
            
            $t_coord = $this->gc->parseGeoCode($this->gc->getGeoCode($t_addr));
            if ($t_coord && ($t_coord != "0,0")) {
                echo ": " . $t_coord;
                $sql = "INSERT INTO address (street, housenum, coord) VALUES ('" . mysql_escape_string($data["street"]) . "', '" . mysql_escape_string($data["housenum"]) . "', '" . mysql_escape_string($t_coord) . "')";
                $this->db->query($sql);
            }
            echo "\n";
        }
    }
    
    function setCityCoord() {
        $sql = "SELECT city2.inc, address.coord FROM city2, address WHERE city2.street = address.street AND city2.housenum = address.housenum";
        foreach ($this->db->query($sql) as $data) {
            $sql = "UPDATE city2 SET coord = '" . $data['coord'] . "' WHERE inc = " . $data['inc'];
            $this->db->query($sql);
        }
    }
}
